using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entity;

using System.Data;
namespace DAL
{
    public class LinksDAL
    {
        /// <summary>
        /// 配置单件实例
        /// </summary>
        public static readonly LinksDAL Instance = new LinksDAL();
        /// <summary>   
        /// 查询友情链接地址
        /// </summary>
        public int SelectLinksCount(string where)
        {
            int recordCount = 0;
            string sql = "select count(id) from links ";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            try
            {
                recordCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return recordCount;
        }

        /// <summary>
        /// 查询友情链接地址
        /// </summary>
        public List<Links> SelectLinksInfo(int pageSize, int pageIndex, string where)
        {
            Links user = null;
            List<Links> lstUser = new List<Links>();
            SqlDataReader sdr = null;
            string sql = "select * from (select id,title,url,sort,created,is_display,entitle,row_number() over (order by id desc ) as pageId from [links]  ";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            sql += ") as t where pageId between (" + pageIndex + "-1)*" + pageSize + " +1 and " + pageIndex + "*" + pageSize;
            try
            {

                using (sdr =
                    SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
                    System.Data.CommandType.Text, sql, null))
                {
                    while (sdr.Read())
                    {
                        user = new Links()
                        {
                            Id = sdr.GetInt32(0),
                            Title = sdr.GetString(1),
                            Url = sdr.GetString(2),
                            Sort = sdr.IsDBNull(3) ? 0 : sdr.GetInt32(3),
                            Created = sdr.IsDBNull(4) ? "" : sdr.GetString(4),
                            Is_display = sdr.IsDBNull(5) ? "" : sdr.GetString(5),
                            ENTitle = sdr.IsDBNull(6) ? "" : sdr.GetString(6)
                        };
                        lstUser.Add(user);
                    }
                }
            }
            catch { }
            finally
            {
                if (sdr != null && !sdr.IsClosed)
                    sdr.Close();
            }
            return lstUser;
        }

        /// <summary>
        /// 查询友情链接地址
        /// </summary>
        public Links SelectLinksInfoForId(string id)
        {
            Links user = null;
            SqlDataReader sdr = null;
            string sql = "select id,title,url,sort,created,is_display,entitle from [links] where id="+id;
            try
            {
                using (sdr =
                    SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
                    System.Data.CommandType.Text, sql, null))
                {
                    while (sdr.Read())
                    {
                        user = new Links()
                        {
                            Id = sdr.GetInt32(0),
                            Title = sdr.GetString(1),
                            Url = sdr.GetString(2),
                            Sort = sdr.IsDBNull(3) ? 0 : sdr.GetInt32(3),
                            Created = sdr.IsDBNull(4) ? "" : sdr.GetString(4),
                            Is_display = sdr.IsDBNull(5) ? "" : sdr.GetString(5),
                            ENTitle = sdr.IsDBNull(6) ? "" : sdr.GetString(6)
                        };
                    }
                }
            }
            catch { }
            finally
            {
                if (sdr != null && !sdr.IsClosed)
                    sdr.Close();
            }
            return user;
        }

        
        /// <summary>
        /// 根据网站链接
        /// </summary>
        public Boolean DeleteLinksForId(string id)
        {
            int recordCount = 0;
            string sql = " delete from links where id=" + id;
            try
            {
                recordCount = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
                if (recordCount > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }


        /// <summary>
        /// 新增网站友情链接
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int InsertLinks(Links link)
        {
            int count = 0;
            string sql = "insert into Links(title,entitle,url,sort,created) values(@title,@entitle,@url,@sort,@created) ";
            try
            {
                SqlParameter[] para = {
                    new SqlParameter("@title",SqlDbType.VarChar),
				    new SqlParameter("@entitle",SqlDbType.VarChar),
				    new SqlParameter("@url",SqlDbType.VarChar),
				    new SqlParameter("@sort",SqlDbType.Int),
				    new SqlParameter("@created",SqlDbType.VarChar)
                    
                };
                para[0].Value = link.Title;
                para[1].Value = link.ENTitle;
                para[2].Value = link.Url;
                para[3].Value = link.Sort;
                para[4].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                count = SqlHelper.ExecuteNonQuery(sql, para);
            }
            catch (Exception err) { }
            return count;
        }

        /// <summary>
        /// 新增网站友情链接
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int UpdateLinks(Links link)
        {
            int count = 0;
            string sql = "update Links set title=@title,entitle=@entitle,url=@url,sort=@sort  where id=" + link.Id;
            try
            {
                SqlParameter[] para = {
                    new SqlParameter("@title",SqlDbType.VarChar),
				    new SqlParameter("@entitle",SqlDbType.VarChar),
				    new SqlParameter("@url",SqlDbType.VarChar),
				    new SqlParameter("@sort",SqlDbType.Int)
                };
                para[0].Value = link.Title;
                para[1].Value = link.ENTitle;
                para[2].Value = link.Url;
                para[3].Value = link.Sort;
                count = SqlHelper.ExecuteNonQuery(sql, para);
            }
            catch (Exception err) { }
            return count;
        }
    }
}
